﻿using System;
using IntelligentStorWpfApp.Model;
using IntelligentStorWpfApp.Interface;
using System.Data;
using MySql.Data.MySqlClient;
using IntelligentStorWpfApp.Helper;

namespace IntelligentStorWpfApp.Services
{
    public class ProductInfoServices : ProductInterface
    {
        string sqlStr = "";
        public bool AddProduct(ProductInfo productInfo)
        {
            sqlStr = "INSERT INTO c_productinfo ( ProductId,ProductNum, status, Description, prdUser ) " +
                        "VALUES(@ProductId,@ProductNum, @Status, @Description, @Owner); ";
            MySqlParameter[] paras =
            {
                new MySqlParameter("@ProductID",productInfo.ProductId),
                new MySqlParameter("@ProductNum",productInfo.ProductNum),
                new MySqlParameter("@Status",productInfo.Status),
                new MySqlParameter("@Description",productInfo.Description),
                new MySqlParameter("@Owner",productInfo.Owner)
            };
            return DBHelper.ExecuteNonQuery(sqlStr, paras);
        }

        public bool DeleteProduct(int Id)
        {
            sqlStr = "delete from c_productinfo where Id=@Id";
            MySqlParameter[] paras =
           {
                new MySqlParameter("@Id",Id)
            };
            return DBHelper.ExecuteNonQuery(sqlStr, paras);
        }

        public DataTable GetProducts(ProductInfo productInfo)
        {
            sqlStr = "SELECT " +
                "c.Id as ID," +
                " c.prdUser as UserName," +
                "c.ProductId as ProductId," +
                "c.status as Pstatus," +
                "c.ProductNum as ProductNum," +
                "c.Description as Description," +
                "m.cabinetNum as cabinetNum," +
                "m.positionNum as positionNum FROM" +
               " c_productinfo c" +
                " LEFT JOIN c_linkprodposition n ON c.ProductId = n.ProductId " +
                "LEFT JOIN c_position m ON n.PositionId = m.Id ";

            if (!string.IsNullOrEmpty(productInfo.ProductId))
            {
                sqlStr = string.Concat(sqlStr, " where c.ProductId like @ProductID;");

                    MySqlParameter[] paras =
                {
                    new MySqlParameter("@ProductID",productInfo.ProductId)
                };
                return DBHelper.ExecuteDataSet(sqlStr, paras);
            }
            else
            {
                return DBHelper.ExecuteDataSet(sqlStr, null);
            }
        }

        public DataTable GetProductsByUser(string userName)
        {
            sqlStr = "SELECT " +
                "c.Id as ID,"+
                " c.prdUser as UserName," +
                "c.ProductId as ProductId,"+
                "c.status as Pstatus," +
                "c.ProductNum as ProductNum," +
                "c.Description as Description," +
                "m.cabinetNum as cabinetNum," +
                "m.positionNum as positionNum FROM" +
               " c_productinfo c" +
                " LEFT JOIN c_linkprodposition n ON c.ProductId = n.ProductId " +
                "LEFT JOIN c_position m ON n.PositionId = m.Id " +
            "WHERE c.prdUser =@Owner ; ";

            MySqlParameter[] paras =
            {
                new MySqlParameter("@Owner",userName)
            };
            return DBHelper.ExecuteDataSet(sqlStr, paras);
        }

        public bool GetProductByPId(string pId)
        {
            sqlStr = "SELECT * FROM c_productinfo c WHERE c.ProductId=@ProductId;";
            MySqlParameter[] paras =
            {
                new MySqlParameter("@ProductId",pId)
            };
            return  DBHelper.ExecuteScalar(sqlStr, paras);
        }

        public bool linkProductPosition(string productId,int positionId)
        {
            sqlStr = "INSERT INTO c_linkprodposition ( ProductId, PositionId )"+
            "VALUES (@ProductId,@PositionId); ";
            MySqlParameter[] paras =
            {
                new MySqlParameter("@ProductId",productId),
                new MySqlParameter("@PositionId",positionId)
            };

            return DBHelper.ExecuteNonQuery(sqlStr,paras);
        }

        public DataTable  checkLink(int Id)
        {
            
            sqlStr = "SELECT * FROM c_linkprodposition where PositionId=@PositionId;";

            MySqlParameter[] paras =
            {
                new MySqlParameter("@PositionId",Id)
            };

            return DBHelper.ExecuteDataSet(sqlStr, paras);
        }

        public bool deleteLinkByProduct(string productId)
        {
            sqlStr = "delete from c_linkprodposition where ProductId=@ProductId; ";
            MySqlParameter[] paras =
            {
                new MySqlParameter("@ProductId",productId)
            };

            return DBHelper.ExecuteNonQuery(sqlStr, paras);
        }



        public bool UpdateProduct(ProductInfo productInfo)
        {
            sqlStr = "UPDATE c_productinfo SET ProductNum = @ProductNum, status = @status, Description = @Description WHERE ProductId=@ProductId;";
            MySqlParameter[] paras =
            {
                new MySqlParameter("@ProductNum",productInfo.ProductNum),
                new MySqlParameter("@status",productInfo.Status),
                new MySqlParameter("@Description",productInfo.Description),
                new MySqlParameter("@ProductId",productInfo.ProductId)
            };
            return DBHelper.ExecuteNonQuery(sqlStr, paras);
        }

        public bool deleteLinkByPosition(int PositionId)
        {
            throw new NotImplementedException();
        }

        public DataTable getAllonlyProducts()
        {
            sqlStr = "select * from c_productinfo;";
            return DBHelper.ExecuteDataSet(sqlStr, null);
        }

        public DataTable getProductsByStatus(string state)
        {
            sqlStr = "select * from c_productinfo where status=@state";
            MySqlParameter[] paras =
            {
                new MySqlParameter("@state",state),
            };
            return DBHelper.ExecuteDataSet(sqlStr,paras);
        }

        public DataTable GetAllStatus()
        {
            sqlStr = "select DISTINCT status from c_productinfo;";
            return DBHelper.ExecuteDataSet(sqlStr, null);
        }
    }
}
